﻿<%@ WebHandler Language="C#" Class="AchievementCollect" %>

using System;
using System.Web;
using System.Text;
using System.Collections.Generic;
using System.Data;
using Chenxin.Lib.Utility;
using Chenxin.Lib.Business;
using Chenxin.Lib.Business.BLL;

public class AchievementCollect : AjaxHandlerBase
{
    public AchievementCollect()
    {
        //导出
        htTypePermit.Add("export", Constants.Permit_AchievementCollect_Export);
    }
    
    override public void RequestProcess(HttpContext context)
    {
        try
        {
            context.Response.ContentType = "text/plain";
            context.Response.AddHeader("pragma", "no-cache");
            context.Response.AddHeader("cache-control", "");
            context.Response.CacheControl = "no-cache";

            string strOp = context.Request["op"];
            string strRet = string.Empty;
            switch (strOp.ToLower())
            {
                //添加报备相关信息
                case "getlist":
                    GetList(context);
                    break;
                case "getpaydetails":
                    GetDetails(context,1);
                    break;
                case "getnopaydetails":
                    GetDetails(context,0);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            context.Response.Write(retExecResult(Constants.ExecResult.Error, ex.Message, ""));
        }
    }
    
    //得到明细数据
    private void GetDetails(HttpContext context, int i)
    {
        //查询条件
        StringBuilder sb = new StringBuilder();
        sb.Append(" 1=1 and Status=1 ");
        //收款时间
        if (!string.IsNullOrEmpty(context.Request["g_stime"]))
            sb.AppendFormat(" and PayDate>='{0}' ", context.Request["g_stime"]);
        if (!string.IsNullOrEmpty(context.Request["g_etime"]))
            sb.AppendFormat(" and PayDate<='{0}' ", context.Request["g_etime"]);
        //实收日期
        if (!string.IsNullOrEmpty(context.Request["stime"]))
        {            
            sb.AppendFormat(" And PayArchYear ={0}", context.Request["stime"].Substring(0, 4));
            sb.AppendFormat(" And PayArchMonth ={0}", context.Request["stime"].Length > 6 ? context.Request["stime"].Substring(5, 2).TrimStart('0') : context.Request["stime"].Substring(5, 1));
        }
        //收款签约点
        if (!string.IsNullOrEmpty(context.Request["GBargainId"]))
            sb.AppendFormat(" and payplaceid='{0}' ", context.Request["GBargainId"]);
        //合同归属
        if (!string.IsNullOrEmpty(context.Request["BargainCompanyId"]))
            sb.AppendFormat(" and BargainCompanyId='{0}' ", context.Request["BargainCompanyId"]);

        //5级
        //Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
        //Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
        //Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
        //Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
        //Guid userId = (string.IsNullOrEmpty(context.Request["userid"])) ? Guid.Empty : new Guid(context.Request["userid"]);

        //if (userId != Guid.Empty)
        //    sb.AppendFormat(" AND (PointEmpId = '{0}') ", userId);
        //else if (departs != Guid.Empty)
        //{
        //    sb.AppendFormat(" and pointdepartid = '{0}'", departs);
        //}
        //else if (businessdistrict != Guid.Empty)
        //{
        //    Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo item = null;
        //    string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, businessdistrict);
        //    Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
        //    item = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
        //    if (!cs.Hit)
        //    {
        //        item = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(businessdistrict);
        //        if (item != null) cs.Set(strCachkey, item, DateTime.Now.AddDays(1));
        //    }

        //    if (item == null)
        //    {
        //        context.Response.Write(retExecResult(Constants.ExecResult.Error, "获取商圈或分类数据丢失", ""));
        //    }
        //    sb.AppendFormat(" and (pointdepartid in (Select DepartId from v_Hr_DepartmentInfo where DepartSrc like '{0}%'))", item.DepartSrc);
        //}
        //else if (company != Guid.Empty)
        //{
        //    sb.AppendFormat(" and (pointdepartid in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", company);
        //}
        //else if (city != Guid.Empty)
        //{
        //    sb.AppendFormat(" and (pointdepartid in (Select DepartId from v_Hr_DepartmentInfo WHERE cityid='{0}')) ", city);
        //}
        //else
        //{
        //    sb.AppendFormat(" and (pointdepartid in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", CurrentUserInfo.CompanyId);
        //}

        //0:只收，1:收支
        if (i == 0)
            sb.Append(" and InOrOut=1 ");
        //50:合计
        if (context.Request["type"] != "50")
            sb.AppendFormat(" and BusinessType='{0}'", context.Request["type"]);
        //部门id
        sb.AppendFormat(" and PointDepartId='{0}'", context.Request["PointDepartId"]);

        string strSql = "select PointDepartId,dbo.FN_GetBussinessByDepartID(PointDepartId) as business,dbo.FN_GetDepartNameByDepartID(PointDepartId) as departname,InOrOut,"
                        +"ContractCode,ContractDate,HouseAddress,Point,RealCommisionFinal,CommisionFinal,ConsultingFinal,PayMoney,ApportionPay,ApportionConsultingFinal,BusinessType "
                        +"from v_Contract_FinanceDetailInfo where"+sb.ToString();
        
        //根据sql执行查询
        DataSet ds = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);

        context.Response.Write(string.Format("{{success:1,msg:'',data:{{count:{0},{1}}}}}", ds.Tables[0].Rows.Count, Chenxin.Lib.Utility.JsonHelp.Convert2Json(ds.Tables[0])));
    }
    

    //得到查询列表
    private void GetList(HttpContext context)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" 1=1 ");
        //收款时间
        if (!string.IsNullOrEmpty(context.Request["g_stime"]))
            sb.AppendFormat(" and PayDate>='{0}' ", context.Request["g_stime"]);
        if (!string.IsNullOrEmpty(context.Request["g_etime"]))
            sb.AppendFormat(" and PayDate<='{0}' ", context.Request["g_etime"]);
        //实收日期
        if (!string.IsNullOrEmpty(context.Request["stime"]))
        {
            sb.AppendFormat(" And PayArchYear ={0}", context.Request["stime"].Substring(0, 4));
            sb.AppendFormat(" And PayArchMonth ={0}", context.Request["stime"].Length > 6 ? context.Request["stime"].Substring(5, 2).TrimStart('0') : context.Request["stime"].Substring(5, 1));
        }
        
        //收款签约点
        if (!string.IsNullOrEmpty(context.Request["GBargainId"]))
            sb.AppendFormat(" and payplaceid='{0}' ", context.Request["GBargainId"]);
        //合同归属
        if (!string.IsNullOrEmpty(context.Request["BargainCompanyId"]))
            sb.AppendFormat(" and BargainCompanyId='{0}' ", context.Request["BargainCompanyId"]);
        
        //5级
        Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
        Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
        Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
        Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
        Guid userId = (string.IsNullOrEmpty(context.Request["userid"])) ? Guid.Empty : new Guid(context.Request["userid"]);

        if (userId != Guid.Empty)
            sb.AppendFormat(" AND (PointEmpId = '{0}') ", userId);
        else if (departs != Guid.Empty)
        {
            sb.AppendFormat(" and pointdepartid = '{0}'", departs);
        }
        else if (businessdistrict != Guid.Empty)
        {
            Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo item = null;
            string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, businessdistrict);
            Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
            item = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
            if (!cs.Hit)
            {
                item = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(businessdistrict);
                if (item != null) cs.Set(strCachkey, item, DateTime.Now.AddDays(1));
            }

            if (item == null)
            {
                context.Response.Write(retExecResult(Constants.ExecResult.Error, "获取商圈或分类数据丢失", ""));
            }
            sb.AppendFormat(" and (pointdepartid in (Select DepartId from v_Hr_DepartmentInfo where DepartSrc like '{0}%'))", item.DepartSrc);
        }
        else if (company != Guid.Empty)
        {
            sb.AppendFormat(" and (pointdepartid in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", company);
        }
        else if (city != Guid.Empty)
        {
            sb.AppendFormat(" and (pointdepartid in (Select DepartId from v_Hr_DepartmentInfo WHERE cityid='{0}')) ", city);
        }
        else
        {
            sb.AppendFormat(" and (pointdepartid in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", CurrentUserInfo.CompanyId);
        }

        sb.Append(" and Status=1 group by pointdepartid  ");
        string strSql = "select pointdepartid,dbo.FN_GetBussinessByDepartID(pointdepartid) as business,dbo.FN_GetDepartNameByDepartID(pointdepartid) as departname," +
            "sum(case when BusinessType=10 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then InOrOut*ApportionPay else 0 end) as amoney," +
            "sum(case when BusinessType=10 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then ApportionPay else 0 end) as a_money,"+

            "sum(case when BusinessType=20 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then InOrOut*ApportionPay else 0 end) as bmoney,"+
            "sum(case when BusinessType=20 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then ApportionPay else 0 end) as b_money," +

            "sum(case when BusinessType=30 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then InOrOut*ApportionPay else 0 end) as cmoney,"+
            "sum(case when BusinessType=30 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then ApportionPay else 0 end) as c_money," +
            
            "sum(case when BusinessType=40 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then  InOrOut*ApportionPay else 0 end) as dmoney,"+
            "sum(case when BusinessType=40 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then ApportionPay else 0 end) as d_money," +

            "sum(case when FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then InOrOut*ApportionPay else 0 end) as allmoney," +
            "sum(case when InOrOut=1 AND FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then ApportionPay else 0 end) as all_money " +
            " from dbo.v_Contract_FinanceDetailInfo where " + sb.ToString();
        /*
        string strSql = "select pointdepartid,dbo.FN_GetBussinessByDepartID(pointdepartid) as business,dbo.FN_GetDepartNameByDepartID(pointdepartid) as departname,"
                        + "sum(case when BusinessType=10 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then ApportionPay else 0 end)-"
                        + "sum(case when BusinessType=10 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as amoney,"
                        + "sum(case when BusinessType=10 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as a_money,"

                        + "sum(case when BusinessType=20 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=20 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as bmoney,"
                        + "sum(case when BusinessType=20 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as b_money,"

                        + "sum(case when BusinessType=30 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=30 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as cmoney,"
                        + "sum(case when BusinessType=30 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as c_money,"

                        + "sum(case when BusinessType=40 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=40 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as dmoney,"
                        + "sum(case when BusinessType=40 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as d_money,"
                        
                        + "sum(case when BusinessType=10 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=10 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)+"
                        + "sum(case when BusinessType=20 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=20 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)+"
                        + "sum(case when BusinessType=30 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=30 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)+"
                        + "sum(case when BusinessType=40 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)-"
                        + "sum(case when BusinessType=40 and Status=1 and InOrOut=-1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as allmoney,"
                        
                        + "sum(case when BusinessType=10 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)+"
                        + "sum(case when BusinessType=20 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)+"
                        + "sum(case when BusinessType=30 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end)+"                        
                        + "sum(case when BusinessType=40 and Status=1 and InOrOut=1 and FundName in ('上家佣金','下家佣金','上家咨询费','下家咨询费') then PayMoney else 0 end) as all_money"
                                                
                        + " from dbo.v_Contract_FinanceDetailInfo where "+sb.ToString();*/

        //根据sql执行查询
        DataSet ds = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);

        context.Response.Write(string.Format("{{success:1,msg:'',data:{{count:{0},{1}}}}}", ds.Tables[0].Rows.Count, Chenxin.Lib.Utility.JsonHelp.Convert2Json(ds.Tables[0])));
    }
}